package henu.dao.impl;

import henu.bean.Certification;
import henu.bean.Certification_PInfomation;
import henu.dao.CertificationDao;
import henu.util.Dbcp;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

public class CertificationDaoImpl implements CertificationDao{
	@Override
	public Certification_PInfomation findByCnid(String args) {
		// TODO Auto-generated method stub
		Certification_PInfomation certification_pinfo=null;
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		String sql="SELECT * FROM VIEW_CERTIFICATION_PINFO  WHERE CNID=?";
		try {
			certification_pinfo=qr.query(sql, new BeanHandler<Certification_PInfomation>(Certification_PInfomation.class),args);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return certification_pinfo;
	}

	@Override
	public int deleteByCnid(String args) {
		// TODO Auto-generated method stub
		int result=0;
		String sql="DELETE FROM CERTIFICATION WHERE CNID=?";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		try {
			result=qr.update(sql, args);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public List<Certification_PInfomation> findByProperty(String property, String key,
			String comid, int start, int end) {
		// TODO Auto-generated method stub
		List<Certification_PInfomation> list=null;
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		String sql="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (SELECT * FROM VIEW_CERTIFICATION_PINFO  WHERE comid=? AND "+property+"='"+key+"' order by regTime asc) tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
		//String sql = "SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (SELECT * FROM VIEW_INSURANCE_CAR_PINFO where comid=? and "+property+"='"+key+"' order by regTime "+sort+") tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
		try {
			list=qr.query(sql, new BeanListHandler<Certification_PInfomation>(Certification_PInfomation.class),comid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
	public Certification findByUserIdcard(String idCard){
		Certification certification =null;
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		String sql ="SELECT * FROM VIEW_CERTIFICATION_PINFO  WHERE idCard=?";
		try {
			certification =qr.query(sql, new BeanHandler<Certification>(Certification.class),idCard);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return certification;
		
	}

	@Override
	public int update(Certification c) {
		int result = 0;
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		String sql ="UPDATE CERTIFICATION SET cnid=?,idCard=?,name=?,sex=?,birthday=?,address=?,type=?,firstdate=?,validation=?,issuedate=?,issueunit=?,serviceunit=?,copyurl=?";
		Object[] params = {c.getCnid(),c.getIdCard(),c.getName(),c.getSex(),c.getBirthday(),c.getAddress(),c.getType(),c.getFirstdate(),c.getValidation(),c.getIssuedate(),c.getIssueunit(),c.getServiceunit(),c.getCopyurl()};
		try {
			result =qr.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int add(Certification c) {
		int result =0;
		String sql="insert into certification (cnid,idCard,name,sex,birthday,address,photo,type,firstdate,validation,issuedate,issueunit,serviceunit,copyurl,memo) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
		Object[] params ={c.getCnid(),c.getIdCard(),c.getName(),c.getSex(),c.getBirthday(),c.getAddress(),c.getPhoto(),c.getType(),c.getFirstdate(),c.getValidation(),c.getIssuedate(),c.getIssueunit(),c.getServiceunit(),c.getCopyurl(),c.getMemo()};
		try {
			result= qr.update(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	
}
